# encoding=utf-8
import telnetlib
import pymysql
from calendar import Calendar
import requests


def get_device_list():
    # 创建连接
    conn = pymysql.connect(host='rm-uf63r801033wg0e41lo.mysql.rds.aliyuncs.com', port=3306, user='boslend_v4',
                           passwd='iKeO2fVgMDi9JCkh', db="boslend_v4")
    # 创建游标
    cursor = conn.cursor()

    # 执行sql，并返回受影响的行数
    cursor.execute("select * from test_nb_data")
    item = cursor.fetchall()

    # 关闭数据库连接
    conn.close()

    return item


def implement(tn, cmd, sn, multiple, year):
    tn.write(cmd)
    a = []
    data = []
    result = str()
    # 获取命令执行结果
    while True:
        b, c, d = tn.expect(a, timeout=1)
        result += d
        if b == 0:
            print('There are more data!!')
            tn.write(r' ')
        else:
            break
    print('Get result success.')
    result = result.replace(">>>", "")
    # print(result)
    # 获取有效数据
    data = result.split('\r\n')
    # 数据去重
    data_list = []
    for item in data:
        ts = item.split(',')
        if len(ts) == 3:
            time_str = ts[0].strip()
            code_str = ts[1].strip()
            data_str = ts[2].strip()
            if int(code_str) == 192:
                print("time = " + time_str + ", code = " + code_str + ", data = " + data_str)
                column = {'timestamp': time_str, 'deviceName': sn, 'value': data_str, 'multiple': multiple}
                data_list.append(column)

    post_data = dict(
        data=data_list
    )
    # print(post_data)
    if len(data_list) > 0:
        r = requests.post('https://apiv3.bltyun.com/authc/open/saveData', json=post_data)
        res = r.json()
        print(res)


def do_telnet(Host, port, devices, year):
    print(len(devices))
    # 连接Telnet服务器
    # timeout只是在初始化socket连接时起作用，而一旦连接成功后出现等待就不起作用了，比如使用read_until方式获取内容时返回的内容与指定的内容不符合，那么就会造成提示等待的情况，这时timeout是不起作用的，这个socket连接会一直保持着，永生不死.一种解决方案是，使用threading模块，利用线程模式来解决.
    tn = telnetlib.Telnet(Host, port=port,
                          timeout=10)
    tn.set_debuglevel(0)

    tn.read_until("RtServer's net console.")

    tn.write('login -u admin -p admin\n')

    tn.read_until("Login successful.")

    c = Calendar(firstweekday=6)

    for dev in devices:

        print(dev)

        # 创建连接
        conn = pymysql.connect(host='rm-uf63r801033wg0e41lo.mysql.rds.aliyuncs.com', port=3306, user='boslend_v4',
                               passwd='iKeO2fVgMDi9JCkh', db="boslend_v4")
        # 创建游标
        cursor = conn.cursor()

        # 执行sql，并返回受影响的行数
        cursor.execute("select * from test_nb_data_upload where sn = %s AND year = %s", (dev[6], year))
        item = cursor.fetchall()
        if len(item) > 0:
            print("existence :" + dev[6])
            # 关闭数据库连接
            conn.close()
            continue

        conn.close()

        month = 1
        # while (month < 13):
        while (month < 10):
            for item in c.itermonthdates(year, month):
                if month == item.month:
                    day = item.strftime("%Y-%m-%d")
                    cmd = 'readhis2 -tag r.%s.%s.%s -b "%s 00:00:00" -e "%s 23:59:00"\n' % (
                        dev[4][:-2], dev[4][-2:], dev[5], day, day)
                    print(cmd)
                    implement(tn, cmd, dev[6], dev[9], year)
            month = month + 1

        # 创建连接
        conn = pymysql.connect(host='rm-uf63r801033wg0e41lo.mysql.rds.aliyuncs.com', port=3306, user='boslend_v4',
                               passwd='iKeO2fVgMDi9JCkh', db="boslend_v4")
        # 创建游标
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO test_nb_data_upload(dev_id,dev_name,dev_build,collector,function,sn,blt_name,blt_build,multiple,year) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",
            (dev[1], dev[2], dev[3], dev[4], dev[5], dev[6], dev[7], dev[8], dev[9], year))
        # 提交到数据库执行
        conn.commit()
        # 关闭数据库连接
        conn.close()

    tn.close()


if __name__ == '__main__':
    # 配置选项
    year = 2021
    Host = '10.22.107.9'  # Telnet服务器IP
    username = 'admin'  # 登录用户名
    password = 'admin'  # 登录密码
    do_telnet(Host, 6606, get_device_list(), year)
